package utils;


import com.mchange.v2.c3p0.ComboPooledDataSource;
import entities.User;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class C3p0Util {
    private static ComboPooledDataSource sc;
    private static Connection conn;
    private static Statement statement;
    private static ResultSet resultSet;
    static{
        sc=new ComboPooledDataSource();//不传参默认使用第一个配置，传参使用指定配置
        try {
            conn=sc.getConnection();
            statement= conn.createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //查询所有用户
    public static List<User> findAll(){
        try {
            List<User> list=new LinkedList<>();
            ResultSet resultSet = statement.executeQuery("select * from user");
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name=resultSet.getString("name");
                int age=resultSet.getInt("age");
                User user=new User(id,name,age);
                list.add(user);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //根据id查询
    public static User findOne(int id){
        try {
            resultSet = statement.executeQuery("select * from user where id=" + id);
            while (resultSet.next()){
                int id1 = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                User user=new User(id1,name,age);
                return user;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    //添加用户
    public static void addUser(User u){
        try {
            statement.executeUpdate("insert into user(id,name,age) values ("+u.getId()+",\'"+u.getName()+"\',"+ u.getAge()+")");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //根据id删除用户
    public static void delUser(int id){
        try {
            statement.executeUpdate("delete from user where id="+id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    //关闭结果集，sql执行对象，连接
    static void close(){
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}
